import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class BillDAO {
    private Connection connection;

    public BillDAO(Connection connection) {
        this.connection = connection;
    }

    // 根据日期查询账单
    public List<Bill> queryBillsByDate(String date) throws SQLException {
        List<Bill> bills = new ArrayList<>();
        String sql = "SELECT * FROM bills WHERE date = ?";
        
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, date);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                Bill bill = new Bill(
                    rs.getInt("id"),
                    rs.getString("date"),
                    rs.getDouble("amount"),
                    rs.getString("category"),
                    rs.getString("note"),
                    rs.getString("type").equals("income") ? "收入" : "支出"
                );
                bills.add(bill);
            }
        }
        return bills;
    }

    // 设置月度预算
    public void setMonthlyBudget(int month, double budget) throws SQLException {
        String sql = "INSERT INTO budgets (month, budget_limit) VALUES (?, ?) " +
                    "ON DUPLICATE KEY UPDATE budget_limit = ?";
        
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, month);
            stmt.setDouble(2, budget);
            stmt.setDouble(3, budget);
            stmt.executeUpdate();
        }
    }

    // 获取月度统计报告
    public MonthlyReport getMonthlyReport(int month) throws SQLException {
        // 获取预算信息
        String budgetSql = "SELECT * FROM budgets WHERE month = ?";
        double budgetLimit = 0;
        double spent = 0;
        
        try (PreparedStatement stmt = connection.prepareStatement(budgetSql)) {
            stmt.setInt(1, month);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                budgetLimit = rs.getDouble("budget_limit");
                spent = rs.getDouble("spent");
            }
        }

        // 获取月度收支统计
        String statsSql = "SELECT type, SUM(amount) as total FROM bills " +
                         "WHERE EXTRACT(MONTH FROM date) = ? " +
                         "GROUP BY type";
        double totalIncome = 0;
        double totalExpense = 0;
        
        try (PreparedStatement stmt = connection.prepareStatement(statsSql)) {
            stmt.setInt(1, month);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                if (rs.getString("type").equals("income")) {
                    totalIncome = rs.getDouble("total");
                } else {
                    totalExpense = rs.getDouble("total");
                }
            }
        }

        // 获取收入明细
        StringBuilder details = new StringBuilder();
        details.append("\n收入明细：");
        String incomeDetailSql = "SELECT category, SUM(amount) as total " +
                                "FROM bills WHERE EXTRACT(MONTH FROM date) = ? " +
                                "AND type = 'income' " +
                                "GROUP BY category " +
                                "ORDER BY total DESC";
        
        try (PreparedStatement stmt = connection.prepareStatement(incomeDetailSql)) {
            stmt.setInt(1, month);
            ResultSet rs = stmt.executeQuery();
            boolean hasIncome = false;
            
            while (rs.next()) {
                hasIncome = true;
                details.append(String.format("\n  %s: %.2f", 
                    rs.getString("category"), 
                    rs.getDouble("total")));
            }
            
            if (!hasIncome) {
                details.append("\n  无收入记录");
            }
        }
        
        // 获取支出明细
        details.append("\n支出明细：");
        String expenseDetailSql = "SELECT category, SUM(amount) as total " +
                                 "FROM bills WHERE EXTRACT(MONTH FROM date) = ? " +
                                 "AND type = 'expense' " +
                                 "GROUP BY category " +
                                 "ORDER BY total DESC";
        
        try (PreparedStatement stmt = connection.prepareStatement(expenseDetailSql)) {
            stmt.setInt(1, month);
            ResultSet rs = stmt.executeQuery();
            boolean hasExpense = false;
            
            while (rs.next()) {
                hasExpense = true;
                details.append(String.format("\n  %s: %.2f", 
                    rs.getString("category"), 
                    rs.getDouble("total")));
            }
            
            if (!hasExpense) {
                details.append("\n  无支出记录");
            }
        }

        return new MonthlyReport(month, budgetLimit, totalIncome, totalExpense, details.toString());
    }

    // 添加插入账单方法
    public void insertBill(Bill bill) throws SQLException {
        String sql = "INSERT INTO bills (date, amount, category, type, note) VALUES (?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, bill.getDate());
            stmt.setDouble(2, bill.getAmount());
            stmt.setString(3, bill.getCategory());
            stmt.setString(4, bill.getType().equals("收入") ? "income" : "expense");
            stmt.setString(5, bill.getNote());
            stmt.executeUpdate();
        }
    }

    // 添加获取所有账单方法
    public List<Bill> getAllBills() throws SQLException {
        List<Bill> bills = new ArrayList<>();
        String sql = "SELECT * FROM bills ORDER BY date DESC";
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                Bill bill = new Bill(
                    rs.getInt("id"),
                    rs.getString("date"),
                    rs.getDouble("amount"),
                    rs.getString("category"),
                    rs.getString("note"),
                    rs.getString("type").equals("income") ? "收入" : "支出"
                );
                bills.add(bill);
            }
        }
        return bills;
    }
}